/**
 * 创建答卷
 */
export const CREATEPAPER = 'insert into paper (name, user_id, class, time) values(?, ?, ?, ?)'

/**
 * 添加题目
 */
export const ADDSUBJECT = 'insert into paper_subjects (paper_id, subject_id) values(?, ?)'

/**
 * 获取所有的答卷
 */
export const GETALLPAPER = `SELECT 
id,
name,
create_time,
(select json_object("id", c.id, "name", c.name, 'command_word', c.command_word, 'create_time', c.create_time) from class c where c.id=paper.class) classInfo,
(select JSON_ARRAYAGG(json_object(
'id', s.id, 'corrCount', s.corrCount, 'errCount', s.errCount, 'ans', s.ans,
'options', JSON_ARRAY(o.a, o.b, o.c, o.d)
)) from paper_subjects ps left join subjects s on ps.subject_id=s.id left join options o on s.options_id=o.id where paper.id=ps.paper_id) subjects
FROM paper where user_id=?`

/**
 * 获取一个答卷
 */
export const GETONEPAPER = `SELECT 
paper.id,
paper.name,
paper.create_time,
paper.time,
u.username username,
(select json_object("id", c.id, "name", c.name, 'command_word', c.command_word, 'create_time', c.create_time) from class c where c.id=paper.class) classInfo,
(select JSON_ARRAYAGG(json_object(
'id', s.id, 'corrCount', s.corrCount, 'errCount', s.errCount, 'ans', s.ans, 'name', s.name,
'options', JSON_ARRAY(o.a, o.b, o.c, o.d)
)) from paper_subjects ps left join subjects s on ps.subject_id=s.id left join options o on s.options_id=o.id where paper.id=ps.paper_id) subjects
FROM paper left join user u on paper.user_id=u.id where paper.id=?
`
// export const GETONEPAPER = `SELECT 
// id,
// name,
// create_time,
// time,
// (select json_object("id", c.id, "name", c.name, 'command_word', c.command_word, 'create_time', c.create_time) from class c where c.id=paper.class) classInfo,
// (select JSON_ARRAYAGG(json_object(
// 'id', s.id, 'corrCount', s.corrCount, 'errCount', s.errCount, 'ans', s.ans, 'name', s.name,
// 'options', JSON_ARRAY(o.a, o.b, o.c, o.d)
// )) from paper_subjects ps left join subjects s on ps.subject_id=s.id left join options o on s.options_id=o.id where paper.id=ps.paper_id) subjects
// FROM paper where id=?
// `

// export const GETPAPERNAME = `select id from paper`

/**
 * 判断答卷是否存在了
 */
export const ISPAPEREXIST = `
select id from paper where name=?
`

/**
 * 获取所有的试题
 */
export const GETALLSUBJECTS = `
select 
s.id id,
s.errCount errCount,
s.corrCount corrCount,
s.ans ans,
s.score score,
s.name name,
s.create_time, create_time,
JSON_ARRAY(o.a, o.b, o.c, o.d) options
from subjects s left join options o on s.options_id=o.id
`

/**
 * 删除答卷
 */
export const DELPAPER = `delete from paper where id=?`


/**
 * 添加
 */
export const ADDSUBJECTS = `insert into subjects (options_id, ans, score, name, analysis) values(?, ?, ?, ?, ?)`

/**
 * 添加到options
 */
export const ADDOPTIONS =  `insert into options (a, b, c, d) values(?, ?, ?, ?)`


/**
 * 删除试题
 */
export const DELSUBJECT = `delete from subjects where id=?`

/**
 * 获取学生的答卷
 */
// export const GETSTUPAPER = `SELECT 
// id,
// name,
// create_time,
// time
// (select json_object("id", c.id, "name", c.name, 'command_word', c.command_word, 'create_time', c.create_time) from class c where c.id=paper.class) classInfo,
// (select JSON_ARRAYAGG(json_object(
// 'id', s.id, 'corrCount', s.corrCount, 'errCount', s.errCount, 'ans', s.ans,
// 'options', JSON_ARRAY(o.a, o.b, o.c, o.d)
// )) from paper_subjects ps left join subjects s on ps.subject_id=s.id left join options o on s.options_id=o.id where paper.id=ps.paper_id) subjects
// FROM paper where class=?`

export const GETSTUPAPER = `SELECT 
id,
name,
create_time,
(select json_object("id", c.id, "name", c.name, 'command_word', c.command_word, 'create_time', c.create_time) from class c where c.id=paper.class) classInfo,
(select JSON_ARRAYAGG(json_object(
'id', s.id, 'corrCount', s.corrCount, 'errCount', s.errCount, 'ans', s.ans,
'options', JSON_ARRAY(o.a, o.b, o.c, o.d)
)) from paper_subjects ps left join subjects s on ps.subject_id=s.id left join options o on s.options_id=o.id where paper.id=ps.paper_id) subjects
FROM paper`


/**
 * 存储答题的情况
 */
export const ADDANSWER = `
    insert into answer (user_id, time, paper_id, score) values(?, ?, ?, ?)
`

/**
 * 存储试卷对应的答题
 */
export const ADDANSWERSUBJECT  = `
    insert into answer_subject (subject_id, ans, uAns, ans_id, user_id) values(?, ?, ?, ?, ?)
`

/**
 * 获取用户的答题记录
 */
export const GETANSWER = `
SELECT a.id id , p.name name, c.name className,  a.score score, a.time time ,  a.create_time create_time FROM answer a left join paper p on a.paper_id=p.id left join class c on p.class=c.id  where a.user_id=?
`

/**
 * 获取答卷试卷详情
 */
export const GETANSWERDETAIL = `
select a.id, 
a.score score, 
a.time time, 
a.create_time create_time, 
(select count(*) from answer_subject where ans != uAns and ans_id=?) errCount,
(select count(*) from answer_subject where ans = uAns and ans_id=?) corrCount,
(select JSON_OBJECT('name', name, 'paperId', a.paper_id) from paper where id=a.paper_id) paper,
JSON_ARRAYAGG(JSON_OBJECT('ans', answer.ans, 'uAns', answer.uAns, 'id', s.options_id,'name', s.name, 'analysis', s.analysis , 'options', JSON_ARRAY(o.a, o.b, o.c, o.d))) subjects
from answer a left join answer_subject answer on a.id=answer.ans_id 
left join subjects s on answer.subject_id=s.id left join options o on s.options_id=o.id
where a.id=? group by a.id
`

/**
 * 正确数 + 1
 */
export const INCCORR = `update subjects set corrCount=corrCount+1 where id=?`

/**
 * 错误数 + 1
 */
export const INCERR = `update subjects set errCount=corrCount+1 where id=?`

/**
 * 获取题目记录
 */
export const GETSUBJECTRECORD = `
SELECT JSON_ARRAYAGG(JSON_OBJECT('id', ans.id, 'subject_id', subject_id, 'name', s.name, 'uAns', ans.uAns, 'ans', ans.ans, 'options', JSON_ARRAY(o.a, o.b, o.c, o.d))) result from answer_subject ans left join subjects s on ans.subject_id=s.id left join options o on s.options_id=o.id where ans.user_id=?
`

/**
 * 搜题
 */
export const SEARCHSUBJECT = `
SELECT s.id id, s.name name, s.analysis analysis, s.ans ans, JSON_ARRAY(o.a, o.b, o.c, o.d) options FROM subjects s left join options o on s.options_id=o.id  where name like ?
`

/**
 * 获取练习题目
 */
export const GETEXEC = `select s.id id, s.ans ans, s.name name, JSON_ARRAY(o.a, o.b, o.c, o.d) options from subjects s left join options o on s.options_id=o.id  limit ? offset ?`

/**
 * 获取试卷各分数情况
 */
export const GETSCORE = `select score name, count(score) value from answer where paper_id=? GROUP BY score`

/**
 * 获取各个选项
 */
export const GETERROPTIONS = `select aw.subject_id id, aw.ans ans, aw.uAns uAns, s.name name from answer a left join answer_subject aw on a.id=aw.ans_id left join subjects s on aw.subject_id=s.id where a.paper_id=?`

/**
 * 获取各个选项
 */
export const GETOPTIONS = `select s.name name, s.ans ans, JSON_ARRAY(o.a, o.b, o.c, o.d) options, aw.uAns uAns  from answer a left join answer_subject aw on a.id=aw.ans_id left join subjects s on aw.subject_id=s.id left join options o on s.options_id=o.id where a.paper_id=?`

/**
 * 获取所有试卷的名字
 */
export const GETALLPAPERNAMES = `select id, name value from paper where user_id=?`